using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.IO;
using System.Reflection;
using System.Resources;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
//using System.Xml.Linq;


namespace CommonUtils
{
	public class DataUtils
	{
		/// <summary>
		/// Add more manual column.
		/// </summary>
		/// <param name="dt">The Data table.</param>
		/// <param name="arrobjParams">List of 3-element-set.
		///    1. Existing column name.
		///    2. New column name.
		///    3. Type of new column.
		/// </param>
		/// <returns>The result data table.</returns>
		public static DataTable Refine(ref DataTable dt, params object[] arrobjParams)
		{ 
			int n = arrobjParams.Length;
			int nCount = dt.Rows.Count;
			for (int i = 0; i < n; i += 3)
			{ 
				string strColumnName = Convert.ToString(arrobjParams[i]);
				string strColumnNameNew = Convert.ToString(arrobjParams[i + 1]);
				Type tNew = (Type)arrobjParams[i + 2];
				dt.Columns.Add(strColumnNameNew, tNew);
				for (int j = 0; j < nCount; j++)
				{
					dt.Rows[j][strColumnNameNew] = dt.Rows[j][strColumnName];
				}
			}
			return dt;
		}

		/// <summary>
		/// Scan all and add more DataTimeStringColumn from all DateTimeColumn.
		/// </summary>
		/// <param name="dt">Data table</param>
		/// <param name="strFormat">Format to convert</param>
		/// <param name="objDefaultValueIfNull">Default value if null occur.</param>
		/// <returns></returns>
		public static DataTable AddColumnDateTimeString(ref DataTable dt, string strFormat, object objDefaultValueIfNull)
		{
			if (dt == null) return dt;
			int n = dt.Columns.Count;
			for (int i = n; i >= 0; i--)
			{
				if (dt.Columns[i].DataType == typeof(DateTime))
				{
					string strColumnString = dt.Columns[i].ColumnName + "MyString";
					dt.Columns.Add(strColumnString, typeof(string));
					for (int j = 0; j < dt.Rows.Count; j++)
					{
						dt.Rows[j][strColumnString] = (Convert.ToString(dt.Rows[j][i]) != "" ? Convert.ToDateTime(dt.Rows[j][i]).ToString(strFormat): objDefaultValueIfNull);
					}
				}
			}
			return dt;
		}
		/// <summary>
		/// Add more DataTimeStringColumn from DateTimeColumn.
		/// </summary>
		/// <param name="dt">Data table</param>
		/// <param name="arrobjParams">List of 4-element-set.
		///    1. Existing column name.
		///    2. New column name.
		///    3. Format to convert.
		///    4. Default value if null occur.
		/// </param>
		/// <returns></returns>
		public static DataTable AddColumnDateTimeString(ref DataTable dt, params object[] arrobjParams)
		{
			if (dt == null || arrobjParams == null) return dt;
			for (int i = 3; i < arrobjParams.Length; i += 4)
			{
				string strColumnNameOld = Convert.ToString(arrobjParams[i - 3]);
				string strColumnNameNew = Convert.ToString(arrobjParams[i - 2]);
				string strFormat = Convert.ToString(arrobjParams[i - 1]);
				object objDefaultValueIfNull = arrobjParams[i];
				dt.Columns.Add(strColumnNameNew, typeof(string));
				for (int j = 0; j < dt.Rows.Count; j++)
				{
					dt.Rows[j][strColumnNameNew] = (Convert.ToString(dt.Rows[j][strColumnNameOld]) != "" ? Convert.ToDateTime(dt.Rows[j][strColumnNameOld]).ToString(strFormat): objDefaultValueIfNull);
				}
			}
			return dt;
		}

		/// <summary>
		/// Scan all and convert all DateTimeColumn to StringColumn in all DataTable of DataSet.
		/// </summary>
		/// <param name="dt">Data set</param>
		/// <param name="strFormat">Format to convert</param>
		/// <param name="objDefaultValueIfNull">Default value if null occur.</param>
		/// <returns></returns>
		public static DataSet ColumnDateTime2ColumnString(ref DataSet ds, string strFormat, object objDefaultValueIfNull)
		{
			if (ds == null) return ds;
			for (int i = 0; i < ds.Tables.Count; i++)
			{
				DataTable dt = ds.Tables[i];
				ColumnDateTime2ColumnString(ref dt, strFormat, objDefaultValueIfNull);
			}
			return ds;
		}
		/// <summary>
		/// Convert DataTimeColumn to StringColumn in all DataTable of DataSet.
		/// </summary>
		/// <param name="dt">Data set</param>
		/// <param name="arrobjParams">List of 4-element-set.
		///    1. Existing column name.
		///    2. Format to convert.
		///    3. Default value if null occur.
		/// </param>
		/// <returns></returns>
		public static DataSet ColumnDateTime2ColumnString(ref DataSet ds, params object[] arrobjParams)
		{
			if (ds == null) return ds;
			for (int i = 0; i < ds.Tables.Count; i++)
			{
				DataTable dt = ds.Tables[i];
				ColumnDateTime2ColumnString(ref dt, arrobjParams);
			}
			return ds;
		}
		/// <summary>
		/// Scan all and convert all DateTimeColumn to StringColumn.
		/// </summary>
		/// <param name="dt">Data table</param>
		/// <param name="strFormat">Format to convert</param>
		/// <param name="objDefaultValueIfNull">Default value if null occur.</param>
		/// <returns></returns>
		public static DataTable ColumnDateTime2ColumnString(ref DataTable dt, string strFormat, object objDefaultValueIfNull)
		{
			if (dt == null) return dt;
			ArrayList alDateTimeColumn = new ArrayList();
			for (int i = 0; i < dt.Columns.Count; i++)
			{
				if (dt.Columns[i].DataType == typeof(DateTime)) alDateTimeColumn.Add(dt.Columns[i].ColumnName);
			}
			if (alDateTimeColumn.Count < 1) return dt;
			object[] arrobjParams = new object[alDateTimeColumn.Count * 3];
			for (int i = 0; i < alDateTimeColumn.Count; i++)
			{
				arrobjParams[3 * i] = alDateTimeColumn[i];
				arrobjParams[3 * i + 1] = strFormat;
				arrobjParams[3 * i + 2] = objDefaultValueIfNull;
			}
			return ColumnDateTime2ColumnString(ref dt, arrobjParams);
		}
		/// <summary>
		/// Convert DataTimeColumn to StringColumn.
		/// </summary>
		/// <param name="dt">Data table</param>
		/// <param name="arrobjParams">List of 4-element-set.
		///    1. Existing column name.
		///    2. Format to convert.
		///    3. Default value if null occur.
		/// </param>
		/// <returns></returns>
		public static DataTable ColumnDateTime2ColumnString(ref DataTable dt, params object[] arrobjParams)
		{
			if (dt == null || arrobjParams == null) return dt;
			for (int i = 2; i < arrobjParams.Length; i += 3)
			{
				string strColumnNameOld = Convert.ToString(arrobjParams[i - 2]);
				string strColumnNameNew = strColumnNameOld + "_MyDateTimeString";
				string strFormat = Convert.ToString(arrobjParams[i - 1]);
				object objDefaultValueIfNull = arrobjParams[i];
				dt.Columns.Add(strColumnNameNew, typeof(string));
				for (int j = 0; j < dt.Rows.Count; j++)
				{
					dt.Rows[j][strColumnNameNew] = (Convert.ToString(dt.Rows[j][strColumnNameOld]) != "" ? Convert.ToDateTime(dt.Rows[j][strColumnNameOld]).ToString(strFormat) : objDefaultValueIfNull);
				}
				dt.Columns.Remove(strColumnNameOld);
				dt.Columns[strColumnNameNew].ColumnName = strColumnNameOld;
			}
			return dt;
		}

		public static DataSet PairedParams2DataSet(params object[] arrobjPairedParams)
		{
			// Check:
			if (arrobjPairedParams == null || arrobjPairedParams.Length < 2) return null;

			// Process:
			DataSet ds = new DataSet("MyDataSet");
			DataTable dt = new DataTable("MyDataTable"); ds.Tables.Add(dt);
			dt.Rows.Add(dt.NewRow());

			for (int i = 2; i < arrobjPairedParams.Length; i += 2)
			{
				string strColumnName = Convert.ToString(arrobjPairedParams[i - 2]);
				dt.Columns.Add(strColumnName, typeof(object));
				dt.Rows[0][strColumnName] = arrobjPairedParams[i - 1];
			}
			ds.AcceptChanges();

			// Return Good:
			return ds;
		}

		public static void UpperCaseAllColumnNames(ref DataSet ds)
		{
			if (ds == null) return;
			foreach (DataTable dt in ds.Tables)
			{
				foreach (DataColumn dc in dt.Columns)
				{
					dc.ColumnName = dc.ColumnName.ToUpper();
				}
			}
		}
		public static void UpperCaseAllColumnNames(ref DataTable dt)
		{
			if (dt == null) return;
			foreach (DataColumn dc in dt.Columns)
			{
				dc.ColumnName = dc.ColumnName.ToUpper();
			}
		}
		public static void StandardizeValuesOfColumns(ref DataTable dt, params string[] arrstrColNames)
		{
			if (dt == null) return;
			if (arrstrColNames == null || arrstrColNames.Length < 1) return;
			for (int i = 0; i < dt.Rows.Count; i++)
			{
				if (dt.Rows[i].RowState == DataRowState.Deleted) continue;
				for (int k = 0; k < arrstrColNames.Length; k++)
				{
					if (dt.Rows[i][arrstrColNames[k]] == DBNull.Value) continue;
					dt.Rows[i][arrstrColNames[k]] = SqlUtils.StandardizeParam(dt.Rows[i][arrstrColNames[k]]);
				}
			}
		}

		/// <summary>
		/// Move dtSource from dsSource to dsDestination.
		/// </summary>
		/// <param name="dsDestination"></param>
		/// <param name="dtSource"></param>
		public static void MoveDataTable(ref DataSet dsDestination, ref DataTable dtSource)
		{
			if (dtSource == null) return;
			DataSet dsSource = dtSource.DataSet;
			if (dsSource != null) dsSource.Tables.Remove(dtSource);
			dsDestination.Tables.Add(dtSource);
		}

		/// <summary>
		/// Move all DataTable from dsSource to dsDestination.
		/// </summary>
		/// <param name="dsDestination"></param>
		/// <param name="dsSource"></param>
		public static void MoveDataTable(ref DataSet dsDestination, ref DataSet dsSource)
		{
			if (dsSource == null) return;
			for (int i = dsSource.Tables.Count - 1; i >= 0; i--) // We must scan from last to first because of SideEffectProblem.
			{
				DataTable dt = dsSource.Tables[i];
				dsSource.Tables.Remove(dt);
				dsDestination.Tables.Add(dt);
			}
		}

		/// <summary>
		/// Create a copy of dtSource based on arrstrCoupleColumnMappingList.
		/// </summary>
		/// <param name="dtSource">dtSource</param>
		/// <param name="strDataTableDestinationName">Name of DestinationDataTable</param>
		/// <param name="arrstrCoupleColumnMappingList">
		///		arrstrCoupleColumnMappingList 
		///		Ex: {
		///				"Code", "CustomerCode"
		///				, "FullName", "CustomerFullName"
		///				, ...
		///			}
		///	</param>
		/// <returns></returns>
		public static DataTable CopyFromDataTable(DataTable dtSource, string strDataTableDestinationName, params string[] arrstrCoupleColumnMappingList)
		{
			// Init:
			DataTable dtDestination = new DataTable(strDataTableDestinationName);
			for (int i = 0; i < arrstrCoupleColumnMappingList.Length; i += 2)
			{
				dtDestination.Columns.Add(arrstrCoupleColumnMappingList[i + 1], dtSource.Columns[arrstrCoupleColumnMappingList[i]].DataType);
			}
			for (int k = 0; k < dtSource.Rows.Count; k++)
			{
				DataRow dr = dtDestination.NewRow(); dtDestination.Rows.Add(dr);
				for (int i = 0; i < arrstrCoupleColumnMappingList.Length; i += 2)
				{
					dr[arrstrCoupleColumnMappingList[i + 1]] = dtSource.Rows[k][arrstrCoupleColumnMappingList[i]];
				}
			}
			dtDestination.AcceptChanges();
			
			// Return Good:
			return dtDestination;
		}

		/// <summary>
		/// AcceptChanges and Reset all DataRowStage to the drsNew.
		/// </summary>
		/// <param name="dt"></param>
		/// <param name="drsNew"></param>
		/// <returns></returns>
		public static DataTable ResetAllDataRowState(ref DataTable dt, DataRowState drsNew)
		{ 
			// Init:
			dt.AcceptChanges();
			for (int i = 0; i < dt.Rows.Count; i++)
			{
				if (drsNew == DataRowState.Deleted)
				{
					dt.Rows[i].Delete();
				}
				else if (drsNew == DataRowState.Modified)
				{
					dt.Rows[i].SetModified();
				}
				else if (drsNew == DataRowState.Added)
				{
					dt.Rows[i].SetAdded();
				}
			}

			// Return Good:
			return dt;
		}
		public static DataSet ResetAllDataRowState(ref DataSet ds, DataRowState drsNew)
		{
			// Init:
			ds.AcceptChanges();
			for (int k = 0; k < ds.Tables.Count; k++)
			{
				DataTable dt = ds.Tables[k];
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					if (drsNew == DataRowState.Deleted)
					{
						dt.Rows[i].Delete();
					}
					else if (drsNew == DataRowState.Modified)
					{
						dt.Rows[i].SetModified();
					}
					else if (drsNew == DataRowState.Added)
					{
						dt.Rows[i].SetAdded();
					}
				}
			}

			// Return Good:
			return ds;
		}

		public static void UnionAll(ref DataTable dtDestination, ref DataTable dtSource, bool bDoAcceptChanges)
		{
			// Check:
			if (dtSource == null) return;
			if (dtDestination == null)
			{
				dtDestination = dtSource;
				return;
			}

			// Process:
			if (bDoAcceptChanges) dtSource.AcceptChanges();
			for (int i = 0; i < dtSource.Rows.Count; i++)
			{
				dtDestination.Rows.Add(dtSource.Rows[i].ItemArray);
			}
			if (bDoAcceptChanges) dtDestination.AcceptChanges();
		}
		public static void UnionAll(ref DataTable dtDestination, DataTable dtSource)
		{
			// Check:
			if (dtSource == null) return;
			if (dtDestination == null)
			{
				dtDestination = dtSource.Copy();
				dtDestination.AcceptChanges();
				return;
			}

			// Process:
			for (int i = 0; i < dtSource.Rows.Count; i++)
			{
				dtDestination.Rows.Add(dtSource.Rows[i].ItemArray);
			}
			dtDestination.AcceptChanges();
		}
		public static DataTable UnionAll(DataTable dt1, DataTable dt2)
		{
			// Check:
			if (dt1 == null) return dt2;
			dt1.AcceptChanges();
			if (dt2 == null) return dt1;
			dt2.AcceptChanges();

			// Process:
			DataTable dtx = dt1.Copy();
			UnionAll(ref dtx, dt2);

			// Return Good:
			return dtx;
		}
		public static DataTable Intersect(DataTable dt1, DataTable dt2)
		{
			// Check:
			if (dt1 == null) return null;
			dt1.AcceptChanges();
			if (dt2 == null) return null;
			dt2.AcceptChanges();
			if (dt2.Rows.Count == 0) return null;
			if (dt1.Rows.Count == 0) return null;
			DataTable dtx = null;
			DataTable dty = null;
			if (dt1.Rows.Count < dt2.Rows.Count)
			{
				dtx = dt2;
				dty = dt1;
			}
			else
			{
				dtx = dt1;
				dty = dt2;
			}
			DataTable dtz = dtx.Clone();

			// Process:
			string strTemplate = "";
			for (int i = 0; i < dtx.Columns.Count; i++) strTemplate += string.Format("|{{0}}|", i);
			Hashtable htx = new Hashtable(100);
			for (int i = 0; i < dtx.Rows.Count; i++)
			{
				string strValue = string.Format(strTemplate, dtx.Rows[i].ItemArray);
				if (!htx.ContainsKey(strValue)) htx.Add(strValue, 0);
			}
			for (int i = 0; i < dty.Rows.Count; i++)
			{
				object[] arrobj = dty.Rows[i].ItemArray;
				if (htx.ContainsKey(string.Format(strTemplate, arrobj)))
					dtz.Rows.Add(arrobj);
			}
			dtz.AcceptChanges();

			// Return Good:
			return dtz;
		}
		public static DataTable Intersect(params DataTable[] arrdt)
		{
			// Temp and Check:
			if (arrdt == null || arrdt.Length < 1) return null;
			if (arrdt.Length == 1) return arrdt[0];
			for (int i = 0; i < arrdt.Length; i++)
				if (arrdt[i] == null || arrdt[i].Rows.Count < 1) return null;

			// Sort ascending on Rows.Count (heuristic):
			for (int i = 0; i < arrdt.Length; i++)
			{
				int k = i;
				for (int j = i + 1; j < arrdt.Length; j++)
				{
					if (arrdt[k].Rows.Count > arrdt[j].Rows.Count) k = j;
				}
				if (k != i)
				{
					DataTable dtTemp = arrdt[i];
					arrdt[i] = arrdt[k];
					arrdt[k] = dtTemp;
				}
			}

			// Intersect:
			DataTable dtResult = Intersect(arrdt[0], arrdt[1]);
			for (int i = 2; i < arrdt.Length; i++)
			{
				dtResult = Intersect(dtResult, arrdt[i]);
			}

			// Return Good:
			return dtResult;
		}
	}

	public class DataTableUtils
	{
		#region // Constants:
		public const string c_strJoinType_InnerJoin = "I";
		public const string c_strJoinType_LeftJoin = "L";
		public const string c_strJoinType_RightJoin = "R";
		public const string c_strJoinType_FullJoin = "F";
		public const string c_strJoinType_DescartesMultiply = "D";

		private const char c_cSeparator = (char)1;
		#endregion

		public static DataTable CopySchema(DataTable dtSource, params string[] arrstrColumns)
		{
			// Check:
			if (dtSource == null) return null;

			// Init:
			DataTable dtResult = new DataTable();
			if (arrstrColumns == null || arrstrColumns.Length < 1)
			{
				for (int i = 0; i < dtSource.Columns.Count; i++)
				{
					dtResult.Columns.Add(dtSource.Columns[i].ColumnName, dtSource.Columns[i].DataType);
				}
			}
			else
			{
				for (int i = 0; i < arrstrColumns.Length; i++)
				{
					dtResult.Columns.Add(arrstrColumns[i], dtSource.Columns[arrstrColumns[i]].DataType);
				}
			}

			// Return Good:
			return dtResult;
		}
		public static DataTable CopyShallow(DataTable dtSource, params string[] arrstrColumns)
		{
			// Check:
			if (dtSource == null) return null;

			// Init:
			DataTable dtResult = CopySchema(dtSource, arrstrColumns);
			for (int i = 0; i < dtSource.Rows.Count; i++)
			{
				if (dtSource.Rows[i].RowState == DataRowState.Deleted) continue;
				DataRow dr = dtResult.NewRow(); dtResult.Rows.Add(dr);
				for (int k = 0; k < dtResult.Columns.Count; k++)
				{
					dr[k] = dtSource.Rows[i][dtResult.Columns[k].ColumnName];
				}
			}
			dtResult.AcceptChanges();

			// Return Good:
			return dtResult;
		}
		public static DataTable CopyShadow(DataTable dtSource)
		{
			// Check:
			if (dtSource == null) return null;

			// Return Good:
			return dtSource.Copy();
		}

		public static DataTable Join(
			string strJoinType
			, bool bJoinCaseInsensitive
			, DataTable dtLeft
			, DataTable dtRight
			, IList listDTLeftColumnNames
			, IList listDTRightColumnNames
			, IList listDTLeftKeyNames
			, IList listDTRightKeyNames
			)
		{ 
			// Check:
			if (dtLeft == null || dtRight == null) return null;
			strJoinType = strJoinType.ToUpper();
			int nPosInvalid = ValidateUtils.ValidateParams(
				strJoinType, true, new object[]{c_strJoinType_InnerJoin, c_strJoinType_LeftJoin, c_strJoinType_RightJoin, c_strJoinType_FullJoin, c_strJoinType_DescartesMultiply}
				);
			if (nPosInvalid >= 0) return null;
			if (!StringUtils.StringEqual(strJoinType, c_strJoinType_DescartesMultiply))
			{
				if (listDTLeftKeyNames == null || listDTLeftKeyNames.Count < 1
					|| listDTRightKeyNames == null || listDTRightKeyNames.Count < 1
					|| listDTLeftKeyNames.Count != listDTRightKeyNames.Count
					)
					return null;
			}
			if (StringUtils.StringEqual(strJoinType, c_strJoinType_RightJoin))
			{
				// Transform RightJoin to LeftJoin:
				strJoinType = c_strJoinType_LeftJoin;
				DataTable dt = dtLeft; dtLeft = dtRight; dtRight = dt;
				IList lstCol = listDTLeftColumnNames; listDTLeftColumnNames = listDTRightColumnNames; listDTRightColumnNames = lstCol;
				IList lstKey = listDTLeftKeyNames; listDTLeftKeyNames = listDTRightKeyNames; listDTRightKeyNames = lstKey;
			}
			int nSideCount = 2; // Left and Right.

			#region // Get Columns:
			DataTable dtResult = new DataTable();
			List<string>[] arralDTCol = new List<string>[] { new List<string>(), new List<string>() };
			List<string>[] arralDTColAlias = new List<string>[] { new List<string>(), new List<string>() };
			DataTable[] arrdt = new DataTable[] { dtLeft, dtRight};
			IList[] arrlst = new IList[] { listDTLeftColumnNames, listDTRightColumnNames};
			for (int idx = 0; idx < nSideCount; idx++)
			{
				// Find ColumnNames:
				if (arrlst[idx] != null && arrlst[idx].Count > 0)
				{
					string[] arrstrSeparators = new string[] { " ", "\t", "\r", "\n" };
					for (int i = 0; i < arrlst[idx].Count; i++)
					{
						string strColMix = string.Format("{0}", arrlst[idx][i]).ToUpper();
						string[] arrstrCols = strColMix.Split(arrstrSeparators, StringSplitOptions.RemoveEmptyEntries);
						if (arrstrCols == null || arrstrCols.Length < 1) continue;
						string strCol = arrstrCols[0].Trim();
						string strAlias = (arrstrCols.Length < 2 ? strCol : arrstrCols[1].Trim());
						if (StringUtils.StringEqual(strCol, "*"))
						{
							for (int k = 0; k < arrdt[idx].Columns.Count; k++)
							{
								arralDTCol[idx].Add(arrdt[idx].Columns[k].ColumnName);
								arralDTColAlias[idx].Add(strAlias.Replace("*", arrdt[idx].Columns[k].ColumnName));
							}
						}
						else
						{
							arralDTCol[idx].Add(strCol);
							arralDTColAlias[idx].Add(strAlias);
						}
					}
				} // if
				
				// Build ColumnNames:
				for (int i = 0; i < arralDTCol[idx].Count; i++)
				{
					dtResult.Columns.Add(arralDTColAlias[idx][i], arrdt[idx].Columns[arralDTCol[idx][i]].DataType);
				}
			} // for
			#endregion

			#region // Build HashIndex:
			// Init:
			Hashtable[] arrht = new Hashtable[] { new Hashtable(), new Hashtable()};

			// Switch cases:
			if (StringUtils.StringEqual(strJoinType, c_strJoinType_DescartesMultiply))
			{
				#region // Case DescartesMultiply:
				for (int idx = 0; idx < nSideCount; idx++)
				{
					ArrayList alRows = new ArrayList();
					foreach (DataRow dr in arrdt[idx].Rows)
					{
						if (dr.RowState != DataRowState.Deleted) alRows.Add(dr);
					}
					arrht[idx].Add("", alRows);
				}
				#endregion
			}
			else
			{
				#region // Case InnerJoin, LeftJoin, RightJoin, FullJoin:
				// Init:
				IList[] arrlistKeyNames = new IList[] { listDTLeftKeyNames, listDTRightKeyNames };
				
				// Scan:
				for (int idx = 0; idx < nSideCount; idx++)
				{
					foreach (DataRow dr in arrdt[idx].Rows)
					{
						if (dr.RowState == DataRowState.Deleted) continue;
						string strKeyVal = "";
						foreach (string strKeyName in arrlistKeyNames[idx])
						{
							//strKeyVal += string.Format("|{0}|", dr[strKeyName]);
							strKeyVal += string.Format("{0}{1}", dr[strKeyName], c_cSeparator);
						}
						if (bJoinCaseInsensitive) strKeyVal = strKeyVal.ToUpper();
						if (arrht[idx].ContainsKey(strKeyVal))
						{
							ArrayList alRows = (ArrayList)arrht[idx][strKeyVal];
							alRows.Add(dr);
						}
						else
						{
							ArrayList alRows = new ArrayList();
							alRows.Add(dr);
							arrht[idx].Add(strKeyVal, alRows);
						}
					}
				}
				#endregion
			}
			#endregion

			/// Notes for Now:
			/// + DescartesMultiply be transformed to InnerJoin.
			/// + RightJoin be transformed to LeftJoin.
			/// + LeftJoin will be processed by 2 steps: InnerJoin and LeftExProcess.
			/// + FullJoin will be processed by 3 steps: InnerJoin, LeftExProcess and RightExProcess.
			
			// Init:
			ArrayList alLeftExProcess = new ArrayList();
			Hashtable htLeft = arrht[0];
			Hashtable htRight = arrht[1];

			#region // InnerJoin step:
			foreach (string strKeyVal in htLeft.Keys)
			{
				// Check:
				if (!htRight.ContainsKey(strKeyVal))
				{
					// Mark for LeftExProcess:
					alLeftExProcess.AddRange((ArrayList)htLeft[strKeyVal]);
					continue;
				}
				ArrayList alLeft = (ArrayList)htLeft[strKeyVal];
				ArrayList alRight = (ArrayList)htRight[strKeyVal];

				// Join:
				foreach (DataRow drLeft in alLeft)
				{
					foreach (DataRow drRight in alRight)
					{
						DataRow[] arrdrSource = new DataRow[] { drLeft, drRight };
						DataRow dr = dtResult.NewRow(); dtResult.Rows.Add(dr);
						for (int idx = 0; idx < nSideCount; idx++)
						{
							int n = arralDTCol[idx].Count;
							for (int i = 0; i < n; i++)
							{
								dr[arralDTColAlias[idx][i]] = arrdrSource[idx][arralDTCol[idx][i]];
							}
						}
					}
				}

				// Mark for RightExProcess:
				htRight.Remove(strKeyVal);
			}
			#endregion

			#region // LeftExProcess step:
			if (StringUtils.StringEqual(strJoinType, c_strJoinType_LeftJoin)
				|| StringUtils.StringEqual(strJoinType, c_strJoinType_FullJoin)
				)
			{
				foreach (DataRow drLeft in alLeftExProcess)
				{
					DataRow dr = dtResult.NewRow(); dtResult.Rows.Add(dr);
					int n = arralDTCol[0].Count;
					for (int i = 0; i < n; i++)
					{
						dr[arralDTColAlias[0][i]] = drLeft[arralDTCol[0][i]];
					}
					int m = arralDTCol[1].Count;
					for (int i = 0; i < m; i++)
					{
						dr[arralDTColAlias[1][i]] = DBNull.Value;
					}
				}
			}
			#endregion

			#region // RightExProcess step:
			if (StringUtils.StringEqual(strJoinType, c_strJoinType_FullJoin))
			{
				ArrayList alRightExProcess = new ArrayList();
				foreach (string strKeyVal in htRight.Keys)
				{
					alRightExProcess.AddRange((ArrayList)htRight[strKeyVal]);
				}

				foreach (DataRow drRight in alRightExProcess)
				{
					DataRow dr = dtResult.NewRow(); dtResult.Rows.Add(dr);
					int n = arralDTCol[0].Count;
					for (int i = 0; i < n; i++)
					{
						dr[arralDTColAlias[0][i]] = DBNull.Value;
					}
					int m = arralDTCol[1].Count;
					for (int i = 0; i < m; i++)
					{
						dr[arralDTColAlias[1][i]] = drRight[arralDTCol[1][i]];
					}
				}
			}
			#endregion

			// Return Good:
			dtResult.AcceptChanges();
			return dtResult;
		}

		public static DataTable GroupBy(
			DataTable dtSource
			, bool bGroupByCaseInsensitive
			, string strColumnNameForCount
			, string strColumnNameForDetailList
			, params object[] arrobjColumsToGroup
			)
		{
			// Check:
			if (dtSource == null
				|| arrobjColumsToGroup == null || arrobjColumsToGroup.Length < 1
				) 
				return null;

			// Init:
			DataTable dtResult = new DataTable();
			foreach (string strColName in arrobjColumsToGroup)
			{
				dtResult.Columns.Add(strColName, dtSource.Columns[strColName].DataType);
			}
			dtResult.Columns.Add(strColumnNameForCount, typeof(int));
			dtResult.Columns.Add(strColumnNameForDetailList, typeof(object));

			// Build Hash:
			Hashtable htKey = new Hashtable();
			Hashtable htData = new Hashtable();
			foreach (DataRow dr in dtSource.Rows)
			{
				if (dr.RowState == DataRowState.Deleted) continue;
				string strKeyVal = "";
				foreach (string strColName in arrobjColumsToGroup)
				{
					//strKeyVal += string.Format("|{0}|", dr[strColName]);
					strKeyVal += string.Format("{0}{1}", dr[strColName], c_cSeparator);
				}
				if (bGroupByCaseInsensitive) strKeyVal = strKeyVal.ToUpper();
				if (htKey.ContainsKey(strKeyVal))
				{
					// Data:
					ArrayList alRows = (ArrayList)htData[strKeyVal];
					alRows.Add(dr);
				}
				else
				{
					// Key:
					DataRow drResult = dtResult.NewRow();
					foreach (string strColName in arrobjColumsToGroup)
					{
						drResult[strColName] = dr[strColName];
					}
					htKey.Add(strKeyVal, drResult);

					// Data:
					ArrayList alRows = new ArrayList();
					alRows.Add(dr);
					htData.Add(strKeyVal, alRows);
				}
			}

			// Build Result:
			foreach (string strKeyVal in htKey.Keys)
			{
				ArrayList alRows = (ArrayList)htData[strKeyVal];
				DataRow drResult = (DataRow)htKey[strKeyVal];
				drResult[strColumnNameForCount] = alRows.Count;
				drResult[strColumnNameForDetailList] = alRows;
				dtResult.Rows.Add(drResult);
			}

			// Return Good:
			dtResult.AcceptChanges();
			return dtResult;
		}
	}

	public class SqlUtils
	{
		#region // Temp:
		//public static string SqlParseEnumList(string strParamList, string strSeparator)
		//{
		//    // Check:
		//    if (strParamList == null || string.Equals(strParamList, "")) return "";
		//    strParamList = SqlProtectInjection(strParamList); // Protect against InjectionCode.

		//    // Process:
		//    string[] arrstrParams = strParamList.Split(new string[] { strSeparator }, StringSplitOptions.None);
		//    string strResult = CommonUtils.StringUtils.BuildList(new ArrayList(arrstrParams), "N'", "'", ",");

		//    // Return Good:
		//    return strResult;
		//}
		#endregion

		public static string ProtectInjection(string strParam)
		{
			strParam = strParam.Replace("'", "").Replace("\"", "").Replace("\r", "").Replace("\n", "");
			if (strParam.Contains("--") || strParam.Contains("zzzzClause")) throw new Exception("ErrSqlUtils.InjectionSqlParams");
			return strParam; // Protect against InjectionCode.
		}
		public static string BuildClauseConditionSingle(
			string strOperatorPrefix
			, string strColumnName
			, string strOperator
			, string strParamName
			, string strParamValue
			, ref ArrayList alParamsCoupleSql
			)
		{
			if (strParamValue == null || StringUtils.StringEqual(strParamValue, "")) return "";
			string strClause = string.Format(
				"{0} ({1} {2} {3})"
				, strOperatorPrefix
				, strColumnName
				, strOperator
				, strParamName
				);
			alParamsCoupleSql.AddRange(new object[] { strParamName, strParamValue });
			return strClause;
		}
		public static string BuildClauseConditionList(
			string strOperatorPrefix
			, string strColumnName
			, string strParamList
			, string strSeparator
			)
		{
			if (strParamList == null) return "";
			strParamList = strParamList.Trim().ToUpper();
			if (strParamList.Length < 1) return "";
			strParamList = SqlUtils.ProtectInjection(strParamList); // Protect against InjectionCode.
			ArrayList alParams = new ArrayList(strParamList.Split(new string[] { strSeparator }, StringSplitOptions.None));
			if (alParams.Contains("NULL") && alParams.Contains("NOTNULL")) return "";
			if (alParams.Contains("NOTNULL")) return string.Format("{0} ({1} is not null)", strOperatorPrefix, strColumnName);
			string strClauseForNull = "";
			int nPosNull = alParams.IndexOf("NULL");
			if (nPosNull >= 0)
			{
				alParams.RemoveAt(nPosNull);
				if (alParams.Count < 1)
				{
					return string.Format("{0} ({1} is null)", strOperatorPrefix, strColumnName);
				}
				strClauseForNull = string.Format("({0} is null) or ", strColumnName);
			}
			string strEnum = StringUtils.BuildList(alParams, "N'", "'", ",");
			string strClause = string.Format(
				"{0} ({1}({2} in ({3})))"
				, strOperatorPrefix // {0}
				, strClauseForNull // {1}
				, strColumnName // {2}
				, strEnum // {3}
				);
			return strClause;
		}
		public static string BuildClauseConditionForNullList(
			string strOperatorPrefix
			, string strColumnName
			, string strParamList
			)
		{
			if (strParamList == null || StringUtils.StringEqual(strParamList, "")) return "";
			string strClause = "";
			if (strParamList.Contains("1")
				&& strParamList.Contains("0")
				)
			{
				strClause = "";
			}
			else if (strParamList.Contains("1"))
			{
				strClause = string.Format("{0} ({1} is null)", strOperatorPrefix, strColumnName);
			}
			else if (strParamList.Contains("0"))
			{
				strClause = string.Format("{0} ({1} is not null)", strOperatorPrefix, strColumnName);
			}
			return strClause;
		}
		public static string BuildClause(
			string strOperatorPrefix
			, string strColumnName
			, string strConditionList
			, string strParamPrefix
			, ref ArrayList alParamsCoupleSql
			)
		{
			// Init:
			if (strConditionList == null) return "";
			strConditionList = strConditionList.Trim().ToUpper();
			if (strConditionList.Length < 1) return "";
			string[] arrstrCondition = strConditionList.Split('|');
			string strResult = "";
			
			// Scan:
			for (int i = 0; i < arrstrCondition.Length; i++)
			{
				// Init:
				string strCondition = arrstrCondition[i].Trim();
				if (strCondition.Length < 1) continue;
				string strOperator = "";
				int nCase = 0;

				// Switch case:
				if (strCondition.StartsWith("="))
				{
					nCase = 10;
					strOperator = "=";
				}
				else if (strCondition.StartsWith("!="))
				{
					nCase = 10;
					strOperator = "!=";
				}
				else if (strCondition.StartsWith(">="))
				{
					nCase = 10;
					strOperator = ">=";
				}
				else if (strCondition.StartsWith("<="))
				{
					nCase = 10;
					strOperator = "<=";
				}
				else if (strCondition.StartsWith(">"))
				{
					nCase = 10;
					strOperator = ">";
				}
				else if (strCondition.StartsWith("<"))
				{
					nCase = 10;
					strOperator = "<";
				}
				else if (strCondition.StartsWith("LIKE"))
				{
					nCase = 10;
					strOperator = "LIKE";
				}
				else if (strCondition.StartsWith("NOT LIKE"))
				{
					nCase = 10;
					strOperator = "NOT LIKE";
				}
				else if (strCondition.StartsWith("IS NULL"))
				{
					nCase = 20;
					strOperator = "IS NULL";
				}
				else if (strCondition.StartsWith("IS NOT NULL"))
				{
					nCase = 20;
					strOperator = "IS NOT NULL";
				}
				else if (strCondition.StartsWith("IN"))
				{
					nCase = 30;
					strOperator = "IN";
				}
				else if (strCondition.StartsWith("NOT IN"))
				{
					nCase = 30;
					strOperator = "NOT IN";
				}

				// Process:
				if (nCase == 10) // Case: =, !=, >, <, >=, <=, LIKE, NOT LIKE.
				{ 
					// Template: and (t.Col = @p1).
					string strParamName = string.Format("{0}{1}", strParamPrefix, alParamsCoupleSql.Count / 2);
					string strParamValue = strCondition.Substring(strOperator.Length).Trim();
					strResult += string.Format(" and ({0} {1} {2})", strColumnName, strOperator, strParamName);
					alParamsCoupleSql.AddRange(new object[]{strParamName, strParamValue});
				}
				else if (nCase == 20) // Case: IS NULL, IS NOT NULL.
				{
					// Template: and (t.Col IS NULL).
					strResult += string.Format(" and ({0} {1})", strColumnName, strOperator);
				}
				else if (nCase == 30) // Case: IN, NOT IN.
				{
					// Template: and (t.Col IN (@p1,@p2,@p3)).
					string strParams = strCondition.Substring(strOperator.Length).Trim();
					string[] arrstrParams = strParams.Split(',');
					StringBuilder strbdParamList = new StringBuilder(2000);
					for (int k = 0; k < arrstrParams.Length; k++)
					{
						string strParamName = string.Format("{0}{1}", strParamPrefix, alParamsCoupleSql.Count / 2);
						string strParamValue = arrstrParams[k].Trim();
						strbdParamList.AppendFormat("{0},", strParamName);
						alParamsCoupleSql.AddRange(new object[] { strParamName, strParamValue });
					}
					if (strbdParamList.Length > 0) strbdParamList.Length = strbdParamList.Length - 1; // Remove the "," at the end of string.
					strResult += string.Format(" and ({0} {1} ({2}))", strColumnName, strOperator, strbdParamList);
				}
			}

			// Assign:
			if (!strResult.StartsWith(" and ")) return "";
			strResult = strResult.Substring(" and ".Length);
			strResult = string.Format("{0} ({1})", strOperatorPrefix, strResult);

			// Return Good:
			return strResult;
		}
		public static string StandardizeParam(object objParam)
		{
			return Convert.ToString(objParam).Trim().ToUpper();
		}

		public static bool Like(object objTest, object objPattern, RegexOptions ro)
		{
			// Check:
			if (objTest == null || objTest == DBNull.Value 
				|| objPattern == null || objPattern == DBNull.Value
				) return false;
			string strTest = string.Format("{0}", objTest);
			string strPattern = string.Format("{0}", objPattern);
			if (strPattern.Length < 1)
			{
				if (strTest.Length < 1) return true;
				return false;
			}

			#region // Process the Pattern:
			// Turn "off" all regular expression related syntax in the pattern string:
			strPattern = Regex.Escape(strPattern);

			// Replace the SQL LIKE wildcard metacharacters with the equivalent regular expression metacharacters:
			strPattern = strPattern.Replace("%", ".*?").Replace("_", ".");

			// The previous call to Regex.Escape actually turned off too many metacharacters
			// Ex: those which are recognized by both the regular expression engine 
			// and the SQL LIKE statement ([...] and [^...]). 
			// Those metacharacters have to be manually unescaped here:
			strPattern = strPattern.Replace(@"\[", "[").Replace(@"\]", "]").Replace(@"\^", "^");
			#endregion

			// Return Good:
			return Regex.IsMatch(strTest, strPattern, ro);
		}
		public static bool Like(object objTest, object objPattern)
		{
			return Like(objTest, objPattern, RegexOptions.IgnoreCase);
		}
	}
}
